<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Binding parameters</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Ruby, SQLite, language, binding parameters, database, sqlite3">
<meta name="description" content="In this chapter of the SQLite Ruby tutorial, we bind parameters.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Binding parameters</h1>

<p>
SQL statements are often dynamically built. A user provides some input and
this input is built into the statement. A programmer must be cautious every time 
he deals with an input from a user. It has some serious security implications. 
The recommended way to dynamically build SQL statements is to use parameter binding. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
When we bind parameters, we create placeholders in the statement. The placeholder
is a special mark in the SQL statement. It is often a question mark (?). Later 
a parameter is bound to the placeholder with a bind_param, execute, query etc. methods.
Binding parameters guards the program against SQL injections. It automatically escapes
some special characters and allows them to be handled correctly. 
</p>

<p>
Many databases also increase significantly their performace, when we prepare 
the statements and bind the parameters. In sqlite3 Ruby module the statements are
always prepared. Even if we do not call the <code>prepare</code> method and call
directly the <code>execute</code> method of the database object, the statement
is prepared behind the scenes by the sqlite3 Ruby module. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new "test.db"

    name = "Volkswagen"
    
    stm = db.prepare "SELECT * FROM Cars WHERE Name = ?"
    stm.bind_param 1, name
    rs = stm.execute
    
    row = rs.next    
    puts row.join "\s"
            
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
The example selects a row from the Cars table for a specific car name. 
</p>

<pre class="explanation">
name = "Volkswagen"
</pre>

<p>
This is a value that could come from a user. For example from a HTML form.
</p>

<pre class="explanation">
stm = db.prepare "SELECT * FROM Cars WHERE Name = ?"
</pre>

<p>
The question mark (?) is a placeholder for a value. It is added later in the
script. 
</p>

<pre class="explanation">
stm.bind_param 1, name
rs = stm.execute
</pre>

<p>
With the <code>bind_param</code> method, the name variable is associated
with the placeholder in the statement. The <code>execute</code> method will
return the result set. 
</p>

<pre>
$ ./bindparam1.rb 
8 Volkswagen 21600
</pre>

<p>
Example output.
</p>

<hr class="btm">

<p>
Next we present another way of binding parameters. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new "test.db"

    id = 4
    
    stm = db.prepare "SELECT * FROM Cars WHERE Id = :id"
    rs = stm.execute id
    
    row = rs.next    
    puts row.join "\s"
            
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
We select a row from the Cars table for a specific Id. 
</p>

<pre class="explanation">
stm = db.prepare "SELECT * FROM Cars WHERE Id = :id"
</pre>

<p>
Previously we have seen a question mark as a placeholder. SQLite Ruby 
supports named placeholders too.
</p>

<pre class="explanation">
rs = stm.execute id
</pre>

<p>
The parameter is bound in the <code>execute</code> method. 
</p>

<hr class="btm">

<p>
We provide yet another way for binding parameters. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new "test.db"

    id = 3
    row = db.get_first_row "SELECT * FROM Cars WHERE Id = ?", id       
    puts row.join "\s"
            
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
This time, everything is done in one step. Preparing the statement, 
binding the parameter and executing the statement. 
</p>

<pre class="explanation">
row = db.get_first_row "SELECT * FROM Cars WHERE Id = ?", id    
</pre>

<p>
The <code>get_first_row</code> is a convenience method, where three
things are done in one step.
</p>

<hr class="btm">

<p>
In our final example, we will bind several parameters in one statement.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new ":memory:"
   
    stm = db.prepare "SELECT 2 + ? + 6 + ? + ?"
    stm.bind_params 3, 4, 6
    rs = stm.execute
    
    row = rs.next    
    puts row
            
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
In the example, we have more placeholders in the SQL statement.
</p>

<pre class="explanation">
stm = db.prepare "SELECT 2 + ? + 6 + ? + ?"
</pre>

<p>
There are three placeholders in the SELECT statement. 
</p>

<pre class="explanation">
stm.bind_params 3, 4, 6
</pre>

<p>
We bind three values with the <code>bind_params</code> method.
</p>

<pre>
$ ./bindparams.rb
21
</pre>

<p>
Output.
</p>


<p>
In this part of the SQLite Ruby tutorial we talked about binding parameters.
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 11, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
